Slowly Changing Dimension

Slowly Changing Dimensions (SCD) - dimensions that change slowly over time, rather than changing on regular schedule, time-base. In Data Warehouse there is a need to track changes in dimension attributes in order to report historical data. In other words, implementing one of the SCD types should enable users assigning proper dimension's attribute value for given date. Example of such dimensions could be: customer, geography, employee.

SCD Type 1

This methodology overwrites old with new data, and therefore does not track historical data.The new record will overwrite the existing one directly without tracking history information. This is unusual in the dimensional design for most business cases.

Example of a supplier table:

If the supplier relocates the headquarters CA to Illinois the record would be overwritten with the new changes.

 Advantages

This is the easiest way to handle the Slowly Changing Dimension problem, since there is no need to keep track of the old information.

Disadvantages

All history is lost. By applying this methodology, it is not possible to trace back in history. For example, in this case, the company would not be able to know that Williams lived in New York before.

Usage

About 50% of the time.

When to use Type 1

Type 1 slowly changing dimension should be used when it is not necessary for the data warehouse to keep track of historical changes.

 SCD Type2

SCD type 2 stores the entire history the data in the dimension table. With type 2 we can store unlimited history in the dimension table. In type 2, you can store the data in three different ways. They are

•     Versioning
•     Flagging
•     Effective Date

SCD Type 2 Versioning: In versioning method, a sequence number is used to represent the change. The latest sequence number always represents the current row and the previous sequence numbers represents the past data.

As an example, let’s use the same example of customer who changes the location. Initially the customer is in Illions location and the data in dimension table will look as.

The customer moves from Illions to Seattle and the version number will be incremented. The dimension table will look as.

Now again if the customer is moved to another location, a new record will be inserted into the dimension table with the next version number.

SCD Type 2 Flagging: In flagging method, a flag column is created in the dimension table. The current record will have the flag value as 1 and the previous records will have the flag as 0.

Now for the first time, the customer dimension will look as.

Now when the customer moves to a new location, the old records will be updated with flag value as 0 and the latest record will have the flag value as 1.

SCD Type 2 Effective Date: In Effective Date method, the period of the change is tracked using the start_date and end_date columns (it can be NULL or high date-9999-12-31)in the dimension table.